package com.mantru.iotpot.dao;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.net.wifi.WifiManager;
import android.os.Build;
import android.os.Environment;
import android.provider.Settings;
import android.telephony.TelephonyManager;
import android.text.TextUtils;

import androidx.annotation.Nullable;

import com.mantru.iotpot.ApplicationClass;
import com.mantru.iotpot.modbus.Common;
import com.mantru.iotpot.modbus.WebUtil;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Collection;
import java.util.Enumeration;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

/**
 * SQLite工具类
 * 作者： cmh
 * 日期： 2022/12/19
 */

public class DataDao extends SQLiteOpenHelper
{
    private static String DB_Name = "pot.db";
    private Context main_context;
    private SQLiteDatabase db;

    public DataDao(@Nullable ApplicationClass context) {
        super(context, DB_Name, null, 1);
        main_context = context;
        db = this.getWritableDatabase();
    }

    @Override
    public void onCreate(SQLiteDatabase db) { }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}


    //初始化，创建全部表
    public boolean InitData()
    {
        if (main_context != null)
        {
            ApplicationClass ac = (ApplicationClass)main_context;
            String is_create_database = WebUtil.GetCacheData(ac, "is_create_database");
            if (is_create_database == null || is_create_database.length() <= 0)
            {
                //缓存is_create_database=1表示已建表
                WebUtil.SaveCacheData(ac, "is_create_database", "1");
                //菜谱分类表 t_vegetable_info_type
                t_vegetable_info_type tb1 = new t_vegetable_info_type();
                db.execSQL(tb1.create_sql);
                //菜谱管理表 t_vegetable_info
                t_vegetable_info tb2 = new t_vegetable_info();
                db.execSQL(tb2.create_sql);
                //菜谱管理表--步骤表 t_vegetable_info_steep
                t_vegetable_info_steep tb3 = new t_vegetable_info_steep();
                db.execSQL(tb3.create_sql);
                //菜谱管理表--步骤表--主配菜表 t_vegetable_info_steep_vegetable
                t_vegetable_info_steep_vegetable tb4 = new t_vegetable_info_steep_vegetable();
                db.execSQL(tb4.create_sql);
                //简单清洗配置表 t_public_config_clean
                t_public_config_clean tb5 = new t_public_config_clean();
                db.execSQL(tb5.create_sql);
                //深度清洗配置表 t_public_config_clean_depth
                t_public_config_clean_depth tb6 = new t_public_config_clean_depth();
                db.execSQL(tb6.create_sql);
                //一键养锅配置表 t_public_config_feed_pot
                t_public_config_feed_pot tb9 = new t_public_config_feed_pot();
                db.execSQL(tb9.create_sql);
                //通用配置表
                t_public_config tb7 = new t_public_config();
                db.execSQL(tb7.create_sql);
                //故障记录表
                t_error_record tb8 = new t_error_record();
                db.execSQL(tb8.create_sql);
                //搜索词表
                t_search_text tb10 = new t_search_text();
                db.execSQL(tb10.create_sql);
                //手动炒制配置表1
                t_handing_config_nozzle tb131 = new t_handing_config_nozzle();
                db.execSQL(tb131.create_sql);
                //自动录制配置表1
                t_handing_config_nozzle2 tb132 = new t_handing_config_nozzle2();
                db.execSQL(tb132.create_sql);
                //插入7个配置位的值
                //"生抽", "老抽", "蚝油", "动物油", "植物油", "盐水", "醋", "淀粉水", "烹饪水"
                //水
                InsertHandingConfig1(1, 0, 0, 0, 0, 0, 0, 0, 0, 20);
                //油
                InsertHandingConfig1(2, 0, 0, 0, 0, 20, 0, 0, 0, 0);
                //生粉水
                InsertHandingConfig1(3, 0, 0, 0, 0, 0, 0, 0, 20, 0);
                //组盒123
                InsertHandingConfig1(4, 0, 0, 0, 0, 0, 0, 0, 0, 0);
                InsertHandingConfig1(5, 0, 0, 0, 0, 0, 0, 0, 0, 0);
                InsertHandingConfig1(6, 0, 0, 0, 0, 0, 0, 0, 0, 0);
                //InsertHandingConfig(7, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
                //手动炒制配置表2
                t_handing_config_box tb141 = new t_handing_config_box();
                db.execSQL(tb141.create_sql);
                //自动录制配置表2
                t_handing_config_box2 tb142 = new t_handing_config_box2();
                db.execSQL(tb142.create_sql);
                InsertHandingConfig2(1, 1, 1, 1,1);
                //基础食材表
                t_base_text tb15 = new t_base_text();
                db.execSQL(tb15.create_sql);
                InsertBaseText("土豆");
                InsertBaseText("猪脚");
                InsertBaseText("辣椒");
                InsertBaseText("豆芽");
                //喷泵校准时间记录表
                t_check_nozzle tb16 = new t_check_nozzle();
                db.execSQL(tb16.create_sql);
                long time = Common.GetTimeStamp10(Common.GetDataTimeString().substring(0, 10).trim() + " 00:00:00");
                InsertCheckNozzle(1, time);
                InsertCheckNozzle(2, time);
                InsertCheckNozzle(3, time);
                InsertCheckNozzle(4, time);
                InsertCheckNozzle(5, time);
                InsertCheckNozzle(6, time);
                InsertCheckNozzle(7, time);
                InsertCheckNozzle(8, time);
                InsertCheckNozzle(9, time);
                InsertCheckNozzle(10, time);
                InsertCheckNozzle(11, time);
                //料管清洗配置表
                t_public_config_clean_nozzle tb17 = new t_public_config_clean_nozzle();
                db.execSQL(tb17.create_sql);
                //简单养锅配置表
                t_public_config_bring tb18 = new t_public_config_bring();
                db.execSQL(tb18.create_sql);

                /*2025.05.27  插测试数据 */
                if (ac.is_test == true) {
                    SaveErrorCode("", "0E10", "机芯故障", "IGBT模块短路、过流", "");
                    SaveErrorCode("", "0E11", "机芯故障", "线盘峰值电流过流", "");
                    SaveErrorCode("", "0E12", "机芯故障", "机芯相位异常", "");
                    SaveErrorCode("", "0E13", "机芯故障", "机芯脉宽异常", "");
                    SaveErrorCode("", "0E14", "机芯故障", "机芯脉长异常", "");
                    SaveErrorCode("", "0E15", "机芯故障", "机芯相位异常", "");
                    SaveErrorCode("", "0E16", "机芯故障", "机芯缺相", "");
                    SaveErrorCode("", "0E17", "机芯故障", "机芯缺电", "");
                    SaveErrorCode("", "0E18", "机芯故障", "机芯缺油", "");
                    SaveErrorCode("", "0E52", "机芯故障", "机芯风机故障", "");
                    SaveErrorCode("", "0001", "电机故障(位置/转锅)", "驱动器错误", "");
                    SaveErrorCode("", "0002", "电机故障(位置/转锅)", "过压报警", "");
                    SaveErrorCode("", "0003", "电机故障(位置/转锅)", "功率管过流报警", "");
                    SaveErrorCode("", "0004", "电机故障(位置/转锅)", "过流报警", "");
                    SaveErrorCode("", "0005", "电机故障(位置/转锅)", "欠压报警", "");
                    SaveErrorCode("", "0006", "电机故障(位置/转锅)", "霍尔报警", "");
                    SaveErrorCode("", "0007", "电机故障(位置/转锅)", "堵转报警", "");
                    SaveErrorCode("", "0008", "电机故障(位置/转锅)", "两种以上警告报警", "");
                    SaveErrorCode("", "0009", "电机故障(位置/转锅)", "上电归位失败", "");
                    SaveErrorCode("", "000A", "电机故障(位置/转锅)", "触发上限位", "");
                    SaveErrorCode("", "000B", "电机故障(位置/转锅)", "触发下限位", "");
                    SaveErrorCode("", "000C", "电机故障(位置/转锅)", "哈哈哈", "");
                }
                return true;
            }
        }
        return true;
    }

    //退出账号，删除数据库
    public boolean DropData()
    {
        ApplicationClass ac = (ApplicationClass)main_context;
        String is_create_database = WebUtil.GetCacheData(ac, "is_create_database");
        if (is_create_database != null && is_create_database.equals("1"))
        {
            //缓存is_create_database=1表示已建表
            WebUtil.SaveCacheData(ac, "is_create_database", "");
            //菜谱分类表 t_vegetable_info_type
            t_vegetable_info_type tb1 = new t_vegetable_info_type();
            db.execSQL(tb1.drop_sql);
            //菜谱管理表 t_vegetable_info
            t_vegetable_info tb2 = new t_vegetable_info();
            db.execSQL(tb2.drop_sql);
            //菜谱管理表--步骤表 t_vegetable_info_steep
            t_vegetable_info_steep tb3 = new t_vegetable_info_steep();
            db.execSQL(tb3.drop_sql);
            //菜谱管理表--步骤表--主配菜表 t_vegetable_info_steep_vegetable
            t_vegetable_info_steep_vegetable tb4 = new t_vegetable_info_steep_vegetable();
            db.execSQL(tb4.drop_sql);
            //简单清洗配置表 t_public_config_clean
            t_public_config_clean tb5 = new t_public_config_clean();
            db.execSQL(tb5.drop_sql);
            //深度清洗配置表 t_public_config_clean_depth
            t_public_config_clean_depth tb6 = new t_public_config_clean_depth();
            db.execSQL(tb6.drop_sql);
            //一键养锅配置表 t_public_config_feed_pot
            t_public_config_feed_pot tb9 = new t_public_config_feed_pot();
            db.execSQL(tb9.drop_sql);
            //通用配置表
            t_public_config tb7 = new t_public_config();
            db.execSQL(tb7.drop_sql);
            //故障记录表
            t_error_record tb8 = new t_error_record();
            db.execSQL(tb8.drop_sql);
            //搜索词表
            t_search_text tb10 = new t_search_text();
            db.execSQL(tb10.drop_sql);
            //手动炒制配置表1
            t_handing_config_nozzle tb131 = new t_handing_config_nozzle();
            db.execSQL(tb131.drop_sql);
            //自动录制配置表1
            t_handing_config_nozzle2 tb132 = new t_handing_config_nozzle2();
            db.execSQL(tb132.drop_sql);
            //手动炒制配置表2
            t_handing_config_box tb141 = new t_handing_config_box();
            db.execSQL(tb141.drop_sql);
            //自动录制配置表2
            t_handing_config_box2 tb142 = new t_handing_config_box2();
            db.execSQL(tb142.drop_sql);
            //基础食材表
            t_base_text tb15 = new t_base_text();
            db.execSQL(tb15.drop_sql);
            //喷泵校准时间记录表
            t_check_nozzle tb16 = new t_check_nozzle();
            db.execSQL(tb16.drop_sql);
            //料管清洗配置表
            t_public_config_clean_nozzle tb17 = new t_public_config_clean_nozzle();
            db.execSQL(tb17.drop_sql);
            //简单养锅配置表
            t_public_config_bring tb18 = new t_public_config_bring();
            db.execSQL(tb18.drop_sql);

            return true;
        }

        return false;
    }

    //执行无结果集返回的sql
    //sql = update table_name set name=? where id=?
    //values = new String[]{"小黑",2}
    public void ExecSQL(String sql, String[] values)
    {
        db.execSQL(sql, values);
    }

    //执行插表sql
    public void Insert(String table_name, ContentValues values)
    {
        db.insert(table_name, null, values);
    }

    //执行sql并返回Cursor记录集合
    public Cursor GetCursor(String sql, String[] params)
    {
        Cursor cursor= db.rawQuery(sql, params);
        return cursor;
    }

    //执行sql并返回的记录集合是否大于0行，true表示大于0行
    public boolean IsExists(String sql, String[] params)
    {
        Cursor cursor= db.rawQuery(sql, params);
        boolean result = false;
        if (cursor.getCount() > 0)
            result = true;
        cursor.close();
        return result;
    }

    //Cursor集合转json字符串
    public String Cursor2Json(Cursor cursor)
    {
        String return_data = "";
        String[] field_names = cursor.getColumnNames();
        while (cursor.moveToNext()){
            String sub_data = "";
            for (int i=0; i < field_names.length; i++)
            {
                String field_name = field_names[i];
                String field_value = cursor.getString(i);
                if (field_name == null)
                    field_name = "";
                else
                    field_name = field_name.trim();
                if (field_value == null)
                    field_value = "";
                else
                    field_value = field_value.trim();
                if (field_name.length() > 0)
                {
                    if (sub_data.length() > 0)
                        sub_data += ",";
                    if ((field_name.endsWith("_data") || field_name.endsWith("_obj")) && field_value.startsWith("[") && field_value.endsWith("]"))
                        sub_data += "\"" + field_name + "\":" + field_value + "";
                    else
                        sub_data += "\"" + field_name + "\":\"" + field_value + "\"";
                }
            }
            if (return_data.length() > 0)
                return_data += ",";
            return_data += "{" + sub_data + "}";
        }

        return return_data;
    }
    //Cursor集合中某一行转json字符串
    public String Cursor2Json(Cursor cursor, int index)
    {
        String return_data = "";
        String[] field_names = cursor.getColumnNames();
        if (cursor.moveToPosition(index)){
            String sub_data = "";
            for (int i=0; i < field_names.length; i++)
            {
                String field_name = field_names[i].trim();
                String field_value = cursor.getString(i);
                field_value = field_value.replace("'", "");
                field_value = field_value.replace("\"", "");
                if (field_name.endsWith("_b64"))
                    field_value = Common.Base64Encoder(field_value);
                if (field_name.length() > 0)
                {
                    if (sub_data.length() > 0)
                        sub_data += ",";
                    sub_data += "\"" + field_name + "\":\"" + field_value + "\"";
                }
            }
            if (return_data.length() > 0)
                return_data += ",";
            return_data += "{" + sub_data + "}";
        }

        return return_data;
    }

    //读取通用配置表，以json字符串返回
    public String GetPublicConfig()
    {
        /*
  `device_param_001` varchar(100) DEFAULT '0' COMMENT '生抽-泵：每秒出的重量，单位（克）',
  `device_param_002` varchar(100) DEFAULT '0' COMMENT '老抽-泵：每秒出的重量，单位（克）',
  `device_param_003` varchar(100) DEFAULT '0' COMMENT '蚝油-泵：每秒出的重量，单位（克）',
  `device_param_004` varchar(100) DEFAULT '0' COMMENT '动物油A-泵：每秒出的重量，单位（克）',
  `device_param_005` varchar(100) DEFAULT '0' COMMENT '动物油B-泵：每秒出的重量，单位（克）',
  `device_param_006` varchar(100) DEFAULT '0' COMMENT '植物油-泵：每秒出的重量，单位（克）',
  `device_param_007` varchar(100) DEFAULT '0' COMMENT '盐水-泵：每秒出的重量，单位（克）',
  `device_param_008` varchar(100) DEFAULT '0' COMMENT '醋-泵：每秒出的重量，单位（克）',
  `device_param_009` varchar(100) DEFAULT '0' COMMENT '淀粉水-泵：每秒出的重量，单位（克）',
  `device_param_010` varchar(100) DEFAULT '0' COMMENT '烹饪水-泵：每秒出的重量，单位（克）',
  `device_param_011` varchar(100) DEFAULT '0' COMMENT '洗锅水-泵：每秒出的重量，单位（克）',
  `tts_param_001` varchar(100) DEFAULT NULL COMMENT '科大讯飞：appid',
  `tts_param_002` varchar(100) DEFAULT NULL COMMENT '科大讯飞：发音人：语音播报发音人，默认青年男生',
  `tts_param_003` varchar(100) DEFAULT NULL COMMENT '科大讯飞：ENGINE_TYPE：普通版设置为TYPE_LOCAL，高品质版设置为TYPE_XTTS',
  `tts_param_004` varchar(100) DEFAULT NULL COMMENT '科大讯飞：合成语速：设置合成返回音频的语速，值范围：[0，100]，默认：50',
  `tts_param_005` varchar(100) DEFAULT NULL COMMENT '科大讯飞：合成音量: 设置合成返回音频的音量，值范围：[0，100]，默认：50',
  `tts_param_006` varchar(100) DEFAULT NULL COMMENT '科大讯飞：合成语调：设置合成返回音频的语调，值范围：[0，100]，默认：50'
        */

        String return_data = "{\"device_param_001\":\"33\", \"device_param_002\":\"10\", \"device_param_003\":\"30\", \"device_param_004\":\"33\", \"device_param_005\":\"33\", \"device_param_006\":\"60\",\"device_param_007\":\"33\",\"device_param_008\":\"33\",\"device_param_009\":\"33\",\"device_param_010\":\"33\",\"device_param_011\":\"33\", \"tts_param_001\":\"\", \"tts_param_002\":\"\", \"tts_param_003\":\"\", \"tts_param_004\":\"\", \"tts_param_005\":\"\", \"tts_param_006\":\"\"}";
        try {
            t_public_config ent = new t_public_config();
            Cursor dt = GetCursor(ent.list_sql, ent.GetListValues());
            if (dt.getCount() > 0)
                return_data = Cursor2Json(dt);
            dt.close();
        }catch (Exception ex){
        }
        return return_data;
    }

    //读取洗锅配置表，以json字符串返回
    public String GetCleanConfig()
    {
        String return_data = "";
        try {
            t_public_config_clean ent = new t_public_config_clean();
            Cursor dt = GetCursor(ent.list_sql, ent.GetListValues());
            if (dt.getCount() > 0)
                return_data = Cursor2Json(dt);
            dt.close();
        }catch (Exception ex){
        }
        return return_data;
    }

    //读取养锅配置表，以json字符串返回
    public String GetBringConfig()
    {
        String return_data = "";
        try {
            t_public_config_bring ent = new t_public_config_bring();
            Cursor dt = GetCursor(ent.list_sql, ent.GetListValues());
            if (dt.getCount() > 0)
                return_data = Cursor2Json(dt);
            dt.close();
        }catch (Exception ex){
        }
        return return_data;
    }

    //保存故障记录
    public boolean SaveErrorCode(String error_data_last, String error_code, String error_type, String error_desc, String error_data)
    {
        try{
            //将0012换成E12的格式
            if (error_code.startsWith("00"))
                error_code = "E" + error_code.substring(2).toUpperCase();

            t_error_record tb = new t_error_record();
            tb.timestamp = String.valueOf(Common.GetTimeStamp13());
            tb.error_code = error_code;
            tb.error_type = error_type;
            tb.error_desc = error_desc;
            tb.error_time = Common.GetDataTimeString();
            Insert(tb.table_name, tb.GetInsertContentValues());
        }catch (Exception ex){
        }
        return true;
    }

    //插入手动炒制配置表1
    public boolean InsertHandingConfig1(int config_id, int nozzle1_value, int nozzle2_value, int nozzle3_value, int nozzle4_value, int nozzle5_value, int nozzle6_value, int nozzle7_value, int nozzle8_value, int nozzle9_value)
    {
        try{
            t_handing_config_nozzle tb = new t_handing_config_nozzle();
            tb.config_id = "" + config_id;
            tb.nozzle1_value = "" + nozzle1_value;
            tb.nozzle2_value = "" + nozzle2_value;
            tb.nozzle3_value = "" + nozzle3_value;
            tb.nozzle4_value = "" + nozzle4_value;
            tb.nozzle5_value = "" + nozzle5_value;
            tb.nozzle6_value = "" + nozzle6_value;
            tb.nozzle7_value = "" + nozzle7_value;
            tb.nozzle8_value = "" + nozzle8_value;
            tb.nozzle9_value = "" + nozzle9_value;
            tb.nozzle10_value = "0";
            tb.nozzle11_value = "0";
            Insert(tb.table_name, tb.GetInsertContentValues());

            t_handing_config_nozzle2 tb2 = new t_handing_config_nozzle2();
            tb2.config_id = "" + config_id;
            tb2.nozzle1_value = "" + nozzle1_value;
            tb2.nozzle2_value = "" + nozzle2_value;
            tb2.nozzle3_value = "" + nozzle3_value;
            tb2.nozzle4_value = "" + nozzle4_value;
            tb2.nozzle5_value = "" + nozzle5_value;
            tb2.nozzle6_value = "" + nozzle6_value;
            tb2.nozzle7_value = "" + nozzle7_value;
            tb2.nozzle8_value = "" + nozzle8_value;
            tb2.nozzle9_value = "" + nozzle9_value;
            tb2.nozzle10_value = "0";
            tb2.nozzle11_value = "0";
            Insert(tb2.table_name, tb2.GetInsertContentValues());
        }catch (Exception ex){
        }
        return true;
    }

    //插入手动炒制配置表1
    public boolean InsertHandingConfig2(int config_id, int box1_value, int box2_value, int box3_value, int box4_value)
    {
        try{
            t_handing_config_box tb = new t_handing_config_box();
            tb.config_id = "" + config_id;
            tb.box1_value = "" + box1_value;
            tb.box2_value = "" + box2_value;
            tb.box3_value = "" + box3_value;
            tb.box4_value = "" + box4_value;
            Insert(tb.table_name, tb.GetInsertContentValues());

            t_handing_config_box2 tb2 = new t_handing_config_box2();
            tb2.config_id = "" + config_id;
            tb2.box1_value = "" + box1_value;
            tb2.box2_value = "" + box2_value;
            tb2.box3_value = "" + box3_value;
            tb2.box4_value = "" + box4_value;
            Insert(tb2.table_name, tb2.GetInsertContentValues());
        }catch (Exception ex){
        }
        return true;
    }

    //插入基础食材表
    public boolean InsertBaseText(String base_name)
    {
        try{
            t_base_text tb = new t_base_text();
            tb.base_name = base_name;
            Insert(tb.table_name, tb.GetInsertContentValues());
        }catch (Exception ex){
            ApplicationClass ac = (ApplicationClass)main_context;
            ac.SaveLog("DataDao.InsertBaseText(" + base_name + ")出错：" + ex.getMessage());
        }
        return true;
    }

    //插入喷泵校准时间记录表
    public boolean InsertCheckNozzle(int id, long time)
    {
        try{
            t_check_nozzle tb = new t_check_nozzle();
            tb.nozzle_id = "" + id;
            tb.last_time = "" + time;
            Insert(tb.table_name, tb.GetInsertContentValues());
        }catch (Exception ex){
        }
        return true;
    }

}
